package com.mawujun.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Writer;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.FileCopyUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.ServletRequestDataBinder;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.support.ByteArrayMultipartFileEditor;

import com.mawujun.util.DBToolkit;
import com.mawujun.util.JsonValueProcessorImpl;
import com.mawujun.util.State;
import com.mawujun.util.User;

/**
 * 导入完成后，修改UA_excelToDb_IS中的状态，在导到正式库的时候，也要修改这个状态改回为0
 * @author mwj
 *
 */
@Controller
public class ImportController {
	
	@Autowired
	private DBToolkit dBToolkit;
	@Autowired
	private LogonController logonController;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@RequestMapping("/import.do")
	public void importFile(HttpServletRequest request,@RequestParam("isSpecify")String isSpecify,@RequestParam("model")String model,@RequestParam("file") MultipartFile file, Integer start, Integer limit,
			String sort, String dir,HttpServletResponse response, Writer writer) throws IOException {
		
		User user=(User)request.getSession().getAttribute("user");
		//先把状态变回来
		logonController.setState(user, model, 0,"");
		if (!file.isEmpty()) {
			try {
				byte[] bytes = file.getBytes();
                                
				 if(file.getOriginalFilename()!=null&&!file.getOriginalFilename().equals("")){   
					   
		               String fileName = file.getOriginalFilename(); 
		               //后缀
		               String hz = fileName.substring(fileName.lastIndexOf(".")+1,fileName.length()); 
		               String qz = fileName.substring(0,fileName.lastIndexOf("."));  
		               if(!"xls".equalsIgnoreCase(hz)&&!"xlsx".equalsIgnoreCase(hz)){
		            	   response.setContentType("text/html");
		            	   writer.write("{success:false,errors:{msg:'文件格式不对,请选择excel文件。'}}");
		           		   writer.close();
		            	   return;//返回文件格式不对的异常
		               }
		               //long filesize=file.getSize();   
		               //String strFilePath ="template";   
		               //InputStream input = file.getInputStream();     
		               String sep = System.getProperty("file.separator");

		               //String uploadDir="E:";
		               //String uploadDir=request.getSession().getServletContext().getRealPath("/")+sep+"upload";
		               String uploadDir=request.getSession().getServletContext().getRealPath("/")+sep+"upload";
		               File fileDir=new File(uploadDir);
		               if(!fileDir.exists()){
		            	   fileDir.mkdir();
		               }
		               File uploadedFile = new File(uploadDir + sep+qz+user.getName()+"."+hz);// file.getOriginalFilename());
		               if(!uploadedFile.exists()){
		            	   uploadedFile.deleteOnExit();
		            	   uploadedFile.createNewFile();
		            	   //uploadedFile.mkdir();
		               }
		               
		               FileCopyUtils.copy(bytes, uploadedFile);
		               
		               System.out.println("********************************");
		               System.out.println(uploadedFile.getAbsolutePath());
		               //System.out.println(bytes.length);
		               System.out.println("********************************");

		               //改变状态，变成正在导入
		               logonController.setState(user, model, 1);
		               
		               response.setContentType("text/html");
		        	   writer.write("{success:true,result:{msg:'导入成功。'}}");
		       		   writer.close();
		               
		               //进行导入计算，不需要启动线程
		               inportTempTable(uploadedFile, user,model,isSpecify,response,writer);
		               uploadedFile.deleteOnExit();
		           }    

			} catch (IOException e) {
				// TODO Auto-generated catch block
				logonController.setState(user, model,0);
				e.printStackTrace();
				response.setContentType("text/html");
	     	    writer.write("{success:false,errors:{msg:'文件上传过程中发生错误，请重新上传。'}}");
	    		writer.close();
	     	    return;
			}  catch(CannotGetJdbcConnectionException e){
				logonController.setState(user, model,0);
				e.printStackTrace();
				response.setContentType("text/html");
	     	    writer.write("{success:false,errors:{msg:'不能连接到数据库，请检查。'}}");
	    		writer.close();
	     	    return;
			}catch (Exception e) {
				// TODO Auto-generated catch block
				logonController.setState(user, model,0);
				e.printStackTrace();
				response.setContentType("text/html");
	     	    writer.write("{success:false,errors:{msg:'在把数据插入到零时表的时候发生错误，请重新上传。'}}");
	    		writer.close();
	     	    return;
			}  
			
		} else {
			response.setContentType("text/html");
     	    writer.write("{success:false,errors:{msg:'你没有选择文件。'}}");
    		writer.close();
     	    return;
		}

	}   
	
	public void inportTempTable(File uploadedFile,User user,String model,String isSpecify,HttpServletResponse response, Writer writer) throws IOException{
		
		 if(!isExitTempTable(user)) {
//			 State state=new State(0);
//			 state.setMsg("创建临时表发生错误！");
			 //这里应该改成重试，重新创建
			 logonController.setState(user, model, 0);
			 response.setContentType("text/html");
	     	 writer.write("{success:false,errors:{msg:'创建临时表发生错误，请重新上传。'}}");
	     	 writer.close();
	     	 return; 
		 }
		 FileInputStream is = new FileInputStream(uploadedFile);   
         HSSFWorkbook wb=new HSSFWorkbook(is); 
         List<String> sqls=new ArrayList<String>();
         
         
         //sheet页的数量
         int sheetNum=wb.getNumberOfSheets();   
         //System.out.println("sheetNum:"+sheetNum);
         
         //先计算一共有多少条
         int sum=0;
         int current=0;//当前是第几条
         for(int i=0;i<sheetNum;i++) {
        	 HSSFSheet childSheet = wb.getSheetAt(i); 
        	 int rowNum = childSheet.getLastRowNum();
        	 sum+=rowNum;
         }
         
         
         String custormer_code="";
         
         for(int i=0;i<sheetNum;i++)   
         {
        	 Date sheetDate=new Date();//每个sheet日期必须相同
        	 int breturnflag=0;//是否是红蓝发票，一个sheet页必须全部为正 或全部为负
        	 int breturnflagAAA=0;
        	 
             HSSFSheet childSheet = wb.getSheetAt(i);  
            
             int rowNum = childSheet.getLastRowNum(); //是从0开始的，总的行数要+1，但是排除掉第一行的名称外，是刚刚好的
             //System.out.println("rowNum:"+rowNum);
             //f(childSheet.isActive()){
             //System.out.println("sheetName:"+childSheet.getSheetName()+"----isActive:"+childSheet.isActive());
             //}
             
             
             //
             for(int j=1;j<=rowNum;j++)   
             {   
            	 //StringBuilder sql=new StringBuilder("inser into UA_excelToDb_"+user.getId()+"(sheetName,cCusCode,dcreatedate,product_id,quantity,rateUnitPrice,money) values(");
            	 StringBuilder sql=new StringBuilder("insert into UA_excelToDb_"+user.getId()+" values(");
            	 sql.append("'"+childSheet.getSheetName()+"'");//sheetName
                 HSSFRow row = childSheet.getRow(j); 
                 
                 int cellNum=row.getLastCellNum();                
//                 for(int k=0;k<cellNum;k++) {   
//                	 HSSFCell cell = row.getCell(k);
//                	 String msg ="";
//                	 if(cell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
//                		 msg=cell.getNumericCellValue()+"";
//                	 } else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA){
//                		 //msg=cell.getCellFormula();
//                		 msg=cell.getNumericCellValue()+"";
//                	 }
//                	 else {
//                	      msg=cell.getStringCellValue();   
//                	 }
//                	 //sql.append(",'"+msg+"'");//sheetName
//                	 
//                     System.out.print(msg+" ");   
//                 }
                 
                 //插入客户编号
                 HSSFCell cell0 = row.getCell(0);
                 if(cell0==null || !StringUtils.hasText(cell0.toString())){
                	 String msg="{success:false,errors:{msg:'数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(0+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(0+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 sql.append(",'"+cell0.toString().trim()+"'");
                 //用户编号是否一致
                 if(i==0&&j==1){
                	 custormer_code=cell0.toString().trim();
                 }
                 if(!custormer_code.equals(cell0.toString().trim())){
                	 //如果不相等就异常
                	 String msg="{success:false,errors:{msg:'用户编号不一致（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(0+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="客户编号不一致（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(0+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 
                 
                 
                 
                 
                 
                 
                 //插入开票日期
                 HSSFCell cell1 = row.getCell(1);
                 if(cell1==null || !StringUtils.hasText(cell1.toString())){
                	 String msg="{success:false,errors:{msg:'数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 try {
                	 Date date=cell1.getDateCellValue();
                     String dateStr=DateFormatUtils.ISO_DATE_FORMAT.format(date);
                     sql.append(",'"+dateStr+"'");
                     
                     
                     if(j==1){
                    	sheetDate=date;
                     } else {
                    	 if(sheetDate.compareTo(date)!=0){
                    		 String msg="{success:false,errors:{msg:'发货日期不一致（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。'}}";
                        	 String msg1="发货日期不一致（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。";
                        	 logonController.setState(user, model, 3,msg1);
                        	 response.setContentType("text/html");
                			 //这里如果定位到哪个sheet页，和哪行
                	     	 writer.write(msg);
                	     	 writer.close();
                	     	 return; 	
                    	 }                 	  
                     }
                     //检查日期格式
                 } catch(Exception e){
                	 e.printStackTrace();
                	 String msg="{success:false,errors:{msg:'日期格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="日期格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 
                 
                 
                 
                 
                 
                 
                 //品番
                 HSSFCell cell2 = row.getCell(2);
                 if(cell2==null || !StringUtils.hasText(cell2.toString())){
                	 String msg="{success:false,errors:{msg:'数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(2+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(2+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model,3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 sql.append(",'"+cell2.toString()+"'");
                 
                 
                 
                 
                
                 
                 //数量
                 HSSFCell cell3 = row.getCell(3);
                 if(cell3==null || !StringUtils.hasText(cell3.toString())){
                	 String msg="{success:false,errors:{msg:'数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(3+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(3+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 try {
                	//检查是不是全数字
                	sql.append(","+cell3.getNumericCellValue()+"");
                 } catch(Exception e){
                	 e.printStackTrace();
                	 String msg="{success:false,errors:{msg:'数字格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(3+1)+"列）,请修改为数字后重新上传。'}}";
                	 String msg1="数字格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(3+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 BigDecimal quantity=new BigDecimal(cell3.getNumericCellValue());//数量
                 
                 if(j==1){
                	 if(quantity.doubleValue()<0){
                		 breturnflagAAA=1;
                	 }
                  } else {
                	  if(breturnflag!=breturnflagAAA){
                		  String msg="{success:false,errors:{msg:'红蓝字发票错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。'}}";
                      	 String msg1="红蓝字发票错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(1+1)+"列）,请修改后重新上传。";
                      	 logonController.setState(user, model, 3,msg1);
                      	 response.setContentType("text/html");
              			 //这里如果定位到哪个sheet页，和哪行
              	     	 writer.write(msg);
              	     	 writer.close();
              	     	 return; 
                	  } 	 
                  }
                 
                 
                 
                 
                 
                 
                 
                 //不含税金额
                 HSSFCell cell5 = row.getCell(5);
                 if(cell5==null || !StringUtils.hasText(cell5.toString())){
                	 String msg="{success:false,errors:{msg:'数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(5+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(5+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 try {
                	//检查是不是全数字
                	 sql.append(","+cell5.getNumericCellValue()+"");
                 } catch(Exception e){
                	 e.printStackTrace();
                	 String msg="{success:false,errors:{msg:'数字格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(5+1)+"列）,请修改为数字后重新上传。'}}";
                	 String msg1="数字格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(5+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 BigDecimal moneySui=new BigDecimal(cell5.getNumericCellValue());//不含税金额
                 
                 
                 //注意这里的次序，和文档中是不一样的
                 //不含税单价
                 HSSFCell cell4 = row.getCell(4);
                 if(cell4==null || !StringUtils.hasText(cell4.toString())){
                	 String msg="{success:false,errors:{msg:'数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(4+1)+"列）,请修改后重新上传。'}}";
                	 String msg1="数据不能为空（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(4+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 try {
	                 //检查是不是全数字
                	 cell4.getNumericCellValue();
                	 
                	 //手工计算不含税单价
                	 BigDecimal rateUnitPrice=moneySui.divide(quantity,29, RoundingMode.HALF_UP);
                	 
	                 sql.append(","+rateUnitPrice+"");
                 } catch(Exception e){
                	 e.printStackTrace();
                	 String msg="{success:false,errors:{msg:'数字格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(4+1)+"列）,请修改为数字后重新上传。'}}";
                	 String msg1="数字格式错误（"+childSheet.getSheetName()+"页，第"+(j+1)+"行，第"+(4+1)+"列）,请修改后重新上传。";
                	 logonController.setState(user, model, 3,msg1);
                	 response.setContentType("text/html");
        			 //这里如果定位到哪个sheet页，和哪行
        	     	 writer.write(msg);
        	     	 writer.close();
        	     	 return; 
                 }
                 
                 
                 sql.append(", "+isSpecify);//是否是特殊发票
                 sql.append(", '"+model+"'");//哪个模块的数据
                 sql.append(", '"+breturnflag+"'");//红蓝发票的标志
                 
                 sql.append(")");
                 sqls.add(sql.toString());
                 current++;
//                 DataFormatter format=new DataFormatter();
//                 DecimalFormat de = new DecimalFormat("0.00");
//                 de.format(number)
                 
                 double percent= Math.floor(((double)current/sum)*100)/100;
                 
                 logonController.setState(user, model,1, percent, "正在读取第"+current+"条,一共"+sum+"个,完成"+percent*100+"%。");
                 //System.out.println(Math.floor(((double)current/sum)*10)/10);   
             }   
                
         }   
         

		try {
			dBToolkit.getJdbcTemplate(user.getDbName()).batchUpdate(sqls.toArray(new String[sqls.size()]));
			//sqls.t
		} catch (Exception e) {
			// TODO Auto-generated catch block
			 e.printStackTrace();
			 logonController.setState(user, model, 0);
			 response.setContentType("text/html");
			 //这里如果定位到哪个sheet页，和哪行
	     	 writer.write("{success:false,errors:{msg:'执行导入的时候发生错误，可能文件格式有错误。'}}");
	     	 writer.close();
	     	 return; 
			
		}
         //conn.setAutoCommit(false);
        
         System.out.println("导入结束");
         logonController.setState(user, model,2);//更改状态为完成

	}
	
	public boolean isExitTempTable(User user) {
		String sql="if not exists (select * from sysobjects where name = 'UA_excelToDb_"+user.getId()+"' and xtype='U')" +
			" begin" +
			" create table UA_excelToDb_"+user.getId()+"(" +
			" id   int   primary   key   identity," +
			" sheetName VARCHAR(50)  NOT  NULL ," +//sheet页的名字
			" cCusCode VARCHAR(30)  NOT  NULL," +//客户编码
			" dcreatedate  datetime  NOT  NULL," +//开票日期
			" product_id VARCHAR(30) NOT  NULL," +//品番
			" quantity decimal(13,2) NOT  NULL," +//数量
			" moneySui  decimal(17,10) NOT  NULL," +//不含税金额
			" rateUnitPrice  decimal(16,10) NOT  NULL," +//不含税单价
			" isSpecify  int NOT  NULL," +//是否是专有发票  27是普通发票，26是专有发票
			" model  VARCHAR(30) NOT  NULL," +//哪个模块的
			" breturnflag  int " +//哪个sheet页 是不是红蓝发票  sheetName
			//" constraint PK_cUser_Name_DbName_model PRIMARY KEY CLUSTERED(cUser_Name,DbName,model)" +
			")" +
			" end ";
		
		// Connection conn;
		try {
			dBToolkit.getJdbcTemplate(user.getDbName()).execute(sql);

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return true;
		
	}
	
	@RequestMapping("/salBillVouchList.do")
	public void list(HttpServletRequest request,String isSpecify,String model,@RequestParam("sheetName")String sheetName, Integer start, Integer limit,
			String sort, String dir,HttpServletResponse response, Writer writer) throws IOException {
		User user=(User)request.getSession().getAttribute("user");
		
		System.out.println(request.getParameter("sheetName"));
		boolean isSheetAll=true;
		if(StringUtils.hasText(sheetName) && !"所有".equals(sheetName)){
			sheetName=" and sheetName='"+sheetName+"' ";
			isSheetAll=false;
		} else {
			sheetName="";
		}
		
		//排序
		String orderBy=" ";
		if(StringUtils.hasText(sort)){
			orderBy+=" order by "+sort;
			if(StringUtils.hasText(dir)){
				orderBy+=" "+dir+" ";
			} else {
				orderBy+=" asc ";
			}
			
			
		}
		String sql="SELECT TOP " +limit+" * from UA_excelToDb_"+user.getId()+" where model='"+model+"'"+sheetName+
				" and (ID NOT IN (SELECT TOP "+start+" ID FROM UA_excelToDb_"+user.getId()+"  where model='"+model+"'"+sheetName+orderBy+"))"+orderBy;
		
		
		String sqlCount="select count(id) from UA_excelToDb_"+user.getId()+" where model='"+model+"'"+sheetName;
		int count=0;
		try {

			count=dBToolkit.getJdbcTemplate(user.getDbName()).queryForInt(sqlCount);

			List<Map<String,Object>> list=dBToolkit.getJdbcTemplate(user.getDbName()).queryForList(sql);
			
			JSONObject result= new JSONObject();
			JSONArray root = new JSONArray();
			// 日期转换时，要进行的配置
			JsonConfig cfg = new JsonConfig();
			cfg.registerJsonValueProcessor(java.util.Date.class,new JsonValueProcessorImpl());
			cfg.registerJsonValueProcessor(java.sql.Date.class,new JsonValueProcessorImpl());
			cfg.registerJsonValueProcessor(java.sql.Timestamp.class,new JsonValueProcessorImpl());
			//cfg.setExcludes(new String[]{"accountRoles","groupAccounts"});
			for(Map<String,Object> map:list) {
//				//JSONObject object=JSONObject.fromObject(null, cfg);// new JSONObject();
//				Map<String,Object> object=new HashMap<String,Object>();
//				object.put("id", rs.getString("id"));
//				object.put("cCusCode", rs.getString("cCusCode"));
//				object.put("dcreatedate", new Date(rs.getDate("dcreatedate").getTime()));
//				//object.put("dcreatedate",new Date());
//				object.put("product_id", rs.getString("product_id"));
//				object.put("quantity", rs.getDouble("quantity"));
//				object.put("moneySui", rs.getDouble("moneySui"));
//				object.put("rateUnitPrice", rs.getDouble("rateUnitPrice"));
//				object.put("sheetName", rs.getString("sheetName"));
//				object.put("isSpecify", rs.getInt("isSpecify"));
//				object.put("model", rs.getString("model"));	
//				object.put("breturnflag", rs.getInt("breturnflag"));
				
				JSONObject object1=JSONObject.fromObject(map, cfg);
				//object1.put("dcreatedate", new Date(((Date)map.get("dcreatedate")).getTime()));
				//object1.put("dcreatedate",new Date());
				root.add(object1);
			}
			
			//计算合计金额，数量，单价等
			String sqlTotal="select sum(quantity) as quantity ,sum(moneySui) as moneySui from UA_excelToDb_"+user.getId()+" where model='"+model+"'";
			if(!isSheetAll){//如果不是是所有的sheet页
				//sqlTotal+=" and sheetName='"+sheetName+"' ";
				sqlTotal+=sheetName;
			}
			//rs=dBToolkit.executeQuery(conn, sqlTotal);
			List<Map<String,Object>> totalList=dBToolkit.getJdbcTemplate(user.getDbName()).queryForList(sqlTotal);
			
			for(Map<String,Object> map:totalList) {
				JSONObject object1=JSONObject.fromObject(map, cfg);
				
				//Map<String,Object> object=new HashMap<String,Object>();
				object1.put("id", "total");
				object1.put("cCusCode", "合计：");
				object1.put("dcreatedate", "");
	
				object1.put("product_id", "");
//				object1.put("quantity", rs.getDouble("quantity"));
//				object1.put("moneySui", rs.getDouble("moneySui"));
				object1.put("rateUnitPrice", "");
				object1.put("sheetName", "");
				object1.put("isSpecify", "");
				object1.put("model", model);	
				
				
				
				root.add(object1);
			}
			
			
		    result.put("root", root.toString());
		    result.put("totalCount", count);
		    writer.write(result.toString());
	     	writer.close();
	     	return;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			response.setContentType("text/html");
			 //这里如果定位到哪个sheet页，和哪行
	     	 writer.write("{success:false,errors:{msg:'查询的时候发生错误，请联系管理员。'}}");
	     	 writer.close();
	     	 return; 
		}
		
	}
	
	@RequestMapping("/sheetNameList.do")
	public void sheetNameList(HttpServletRequest request,String isSpecify,String model,Writer writer) throws SQLException, IOException{
		User user=(User)request.getSession().getAttribute("user");
		
		String sql="select distinct sheetName from UA_excelToDb_"+user.getId()+" where model='"+model+"'";
		//Connection conn = dBToolkit.getConnection(user.getDbName());
		//ResultSet rs=dBToolkit.executeQuery(conn, sql);
		List<Map<String,Object>> list=dBToolkit.getJdbcTemplate(user.getDbName()).queryForList(sql);
		JSONArray root = new JSONArray();
		JSONArray all= new JSONArray();
		all.add("所有");
		all.add("所有");
		root.add(all);	
		for(Map<String,Object> map:list) {
			JSONArray object= new JSONArray();
			object.add(map.get("sheetName"));//.put("value", rs.getString("sheetName"));
			object.add(map.get("sheetName"));//.put("name", rs.getString("sheetName"));
			root.add(object);	
		}
		writer.write( root.toString());
     	writer.close();
     	return;
		
	}
	
	
	@RequestMapping("/updateSaleBillVouch.do")
	public void updateSaleBillVouch(HttpServletRequest request,String id,String field,String value,Writer writer) throws IOException{
		User user=(User)request.getSession().getAttribute("user");
		
		String sql="update UA_excelToDb_"+user.getId()+" set "+field+"='"+value+"' where id="+id;
		
		//计算单价
		BigDecimal  rateUnitPrice=new BigDecimal(0);
		if("quantity".equalsIgnoreCase(field)){
			//获取金额
			if(StringUtils.hasText(request.getParameter("moneySui"))){
				BigDecimal moneySui=new BigDecimal(request.getParameter("moneySui"));
				
				if("0".equals(value)){
					rateUnitPrice=moneySui;
				} else {
					rateUnitPrice=moneySui.divide(new BigDecimal(value),29, RoundingMode.HALF_UP);
				}
				
				sql="update UA_excelToDb_"+user.getId()+" set "+field+"='"+value+"' , rateUnitPrice='"+rateUnitPrice+"' where id="+id;
			}
			
		} else if("moneySui".equals(field)){
			//获取数量
			if(StringUtils.hasText(request.getParameter("quantity"))){
				BigDecimal quantity=new BigDecimal(request.getParameter("quantity"));
				
				if(quantity.compareTo(new BigDecimal(0))==0){
					rateUnitPrice=(new BigDecimal(value));
				} else {
					rateUnitPrice=(new BigDecimal(value)).divide(quantity,29, RoundingMode.HALF_UP);
				}
				
				sql="update UA_excelToDb_"+user.getId()+" set "+field+"='"+value+"' , rateUnitPrice='"+rateUnitPrice+"' where id="+id;
			}
		}
		
		
		//System.out.println(sql);
		try {
//			Connection conn = dBToolkit.getConnection(user.getDbName());
//			dBToolkit.executeSQL(conn, sql);
//			dBToolkit.closeConnection(conn);
			dBToolkit.getJdbcTemplate(user.getDbName()).execute(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			System.out.println(sql);
			e.printStackTrace();
			writer.write( "{success:false}");
	     	writer.close();
	     	return;
		}
		
	
		writer.write( "{success:true,rateUnitPrice:"+rateUnitPrice+"}");
     	writer.close();
     	return;
		
	}
	
	@RequestMapping("/deleteSaleBillVouch.do")
	public void updateSaleBillVouch(HttpServletRequest request,String id,Writer writer) throws IOException{
		User user=(User)request.getSession().getAttribute("user");
		
		String sql="delete UA_excelToDb_"+user.getId()+" where id="+id;
		//System.out.println(sql);
		try {
//			Connection conn = dBToolkit.getConnection(user.getDbName());
//			dBToolkit.executeSQL(conn, sql);
//			dBToolkit.closeConnection(conn);
			dBToolkit.getJdbcTemplate(user.getDbName()).execute(sql);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			System.out.println(sql);
			e.printStackTrace();
			writer.write( "{success:false}");
	     	writer.close();
	     	return;
		}
		
	
		writer.write( "{success:true}");
     	writer.close();
     	return;
		
	}
	
	@RequestMapping("/clearAll.do")
	public void clearAll(HttpServletRequest request,String model,String sheetName, Writer writer) throws IOException{
		User user=(User)request.getSession().getAttribute("user");
		
		if(!StringUtils.hasText(model)){
			System.out.println("参数不能为空");
			return;
		}
		String sql="delete UA_excelToDb_"+user.getId()+" where model='"+model+"'";
		if(StringUtils.hasText(sheetName) && !"所有".equals(sheetName)){
			sql="delete UA_excelToDb_"+user.getId()+" where model='"+model+"' and sheetName='"+sheetName+"'";
		}
		
		//System.out.println(sql);
		try {
//			Connection conn = dBToolkit.getConnection(user.getDbName());
//			dBToolkit.executeSQL(conn, sql);
			dBToolkit.getJdbcTemplate(user.getDbName()).execute(sql);
			
			//判断这个model里面是否海鸥数据，如果没有了的话，就更改状态
			//sdf
			String sqlcount="select count(id) from UA_excelToDb_"+user.getId()+" where model='"+model+"'";
//			int size=dBToolkit.executeForInt(conn, sqlcount);
			int size=dBToolkit.getJdbcTemplate(user.getDbName()).queryForInt(sqlcount);
			if(size<=0){
				logonController.setState(user, model, 0);
			}
			
			//dBToolkit.closeConnection(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			System.out.println(sql);
			e.printStackTrace();
			writer.write( "{success:false}");
	     	writer.close();
	     	return;
		}
		
	
		writer.write( "{success:true}");
     	writer.close();
     	return;
		
	}
	
	
	@RequestMapping("/dbToUF.do")
	public void dbToUF(HttpServletRequest request,String model, String startDate,String endDate,Writer writer) throws IOException{
		User user=(User)request.getSession().getAttribute("user");
		
		if(!StringUtils.hasText(model)){
			System.out.println("参数不能为空");
			return;
		}
		
//		//给出一个时间区间的选择，就是把这个“时间区间”内的发货总数量汇总，与EXCEL导进行来的数量进行比对，应该发货数量>=EXCEL里的所有数量才是正确的，否则不让客户导入。
//		String sql11="select sum(iQuantity) as iQuantity from DispatchList a, DispatchLists b " +
//				" where a.dDate between '"+startDate+"' and '"+endDate+"' and a.DLID =b.DLID ";
		
		
		
		
		try {
			//获取客户编码
			String cCusCode="";

			int cVouchType=0;//发票类型
			int iVTid=0;//单据模版号
			//String cInvCode="";//存货编码
			
			String todbSql="select * from UA_excelToDb_"+user.getId()+" where model='"+model+"' order by sheetName,dcreatedate asc,product_id";
			List<Map<String,Object>> todbRsList=dBToolkit.getJdbcTemplate(user.getDbName()).queryForList(todbSql);

			if(todbRsList!=null && todbRsList.size()!=0){
				cCusCode=todbRsList.get(0).get("cCusCode")+"";

				cVouchType=(Integer)todbRsList.get(0).get("isSpecify");
				if(cVouchType==27){
					iVTid=17;
				} else if(cVouchType==26) {
					iVTid=53;
				}
				//cInvCode=todbRsList.get(0).get("product_id")+"";
				
				//sheetName=todbRsList.get(0).get("sheetName")+"";
				//因为一次只能导一个客户
			}
			
			
			
			String sql11="select sum(iQuantity) as iQuantity from DispatchList a, DispatchLists b " +
				" where  a.dDate between '"+startDate+"' and '"+endDate+"' and a.DLID =b.DLID " +
				//" and CINVCODE='"+cInvCode+"'" +//产品,一个excel中，一个EXCEL的产品品番号很多的
				" and a.cCusCode ='"+cCusCode+"'" +//客户编码
				//" and a.SBVID is null";//销售发票还没有关联进去的
				" and isnull(b.iSettleQuantity,0)<>isnull(b.iQuantity,0) "+//已开票数量不等于发货数量的就取出
				"  and a.cVerifier is not null " +//cVerifier就是审核人，当发货单没审核的时候他的数量也不做开票的考虑
				" AND a.cCloser is null ";
			//导到用友的表
			//System.out.println(sql11);

			//获取发货总数
			BigDecimal iQuantitySum=new BigDecimal(0);
			try {
				iQuantitySum=dBToolkit.getJdbcTemplate(user.getDbName()).queryForObject(sql11, BigDecimal.class);
			} catch(EmptyResultDataAccessException e){
				writer.write( "{success:false,errors:{msg:'时间区间 内的发货表中没有符合要求的数据'}}");
		     	writer.close();
				return;
			}
			

			//获取excel中的所有数据
			String sql12="select sum(quantity) as quantity from UA_excelToDb_"+user.getId()+" where model='"+model+"'";

			BigDecimal quantitySum=dBToolkit.getJdbcTemplate(user.getDbName()).queryForObject(sql12, BigDecimal.class);

			//判断两个数量是否一致
			if(iQuantitySum==null || iQuantitySum.intValue()<quantitySum.intValue()){
				//进行错误的判断，并返回错误信息
				writer.write( "{success:false,errors:{msg:'时间区间 内的发货总数量小于等于excel中的发货数量'}}");
		     	writer.close();
				return;
			}
			//获取基础数据
			//获取销售类型编码
			String sql2="select cSTCode from SaleType where bdefault=1";
			String  cSTCode="";
			try {
				Map<String,Object> map2=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(sql2);
				cSTCode=(String)map2.get("cSTCode");
				if(!StringUtils.hasText(cSTCode)){
					cSTCode="";
				}
			} catch(EmptyResultDataAccessException e){

			}
			
			

			
			//部门编码
			String sql3="select cDepCode from department where cdepname like '%营业课%'";
			Map<String,Object> map3=null;
			try {
				map3=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(sql3);
			} catch(EmptyResultDataAccessException e) {
				//e.printStackTrace();
				map3=null;
			}
			String  cDepCode=null;
			if(map3==null){
				cDepCode="";
			} else {
				cDepCode=(String)map3.get("cDepCode");
			}

			
			//币种名称
			String cexch_name="人民币";
			
			//汇率
			int iExchRate =1;
			//表头税率
			int iTaxRate =17;
			//银行编码
			String sql4="select min(cbcode) as cBCode  from bank";
			String cBCode="";
			try {
				Map<String,Object> map4=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(sql4);
				if(map4!=null && map4.get("cBCode")!=null){
					cBCode=(String)map4.get("cBCode");
				}
			} catch(EmptyResultDataAccessException e){

			}
			
			
			
			
			//业务类型
			String cBusType ="普通销售";
			//应收期初标志
			String bFirst   ="0";
			//是否先发货 
			int iDisp=1;
			//单据来源
			String cSource   ="销售";
			
			//审核状态
			String iverifystate  ="0";
			
			//制单日期
			Date dcreatesystime =user.getDate();


			

			
			//获取 客户名称 ，客户联系人 ，客户银行
			String cCusName="";
			String ccusperson="";
			String ccusbank="";
			
			String sql5="select ccusname,ccusperson ,ccusbank  from customer where cCusCode='"+cCusCode+"'";
			//rs=dBToolkit.executeQuery(conn, sql5);
			List<Map<String,Object>> ccusnameList=dBToolkit.getJdbcTemplate(user.getDbName()).queryForList(sql5);
			for(Map<String,Object> map:ccusnameList){
				cCusName=(String)map.get("cCusName");//rs.getString("ccusname");
				ccusperson=(String)map.get("ccusperson");//rs.getString("ccusperson");
				ccusbank=(String)map.get("ccusbank");//rs.getString("ccusbank");
				
			}
			
			//制单人
			String cMaker=user.getName();
			
			//客户账号
			String ccusaccountSql="select distinct ccusaccount from customer where cCusCode='"+cCusCode+"'";
			
			String ccusaccount="";
			try {
				Map<String,Object> ccusaccountMap=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(ccusaccountSql);
				ccusaccount=(String)ccusaccountMap.get("ccusaccount");
				
			} catch(EmptyResultDataAccessException e){

			}

			//ResultSet dispatchRs=dBToolkit.executeQuery(conn, dispatchSql);
			
			//-------------------------
			//扣率
			int KL=100;
			//扣率2
			int KL2=100;
			
				
			//是否记账
			int bcosting=1;
			

			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			
			//-----------------------
			
			//开始导入了
			//ResultSet cSBVCodeRs=null;
			Map<String,Object> cSBVCodeRs=null;
			String sheetName="";
			int SBVID=1;
			List<String> sqls=new ArrayList<String>();
			List<Map<String,Object>> paramsLine=new ArrayList<Map<String,Object>>();
			
			//List<String> updateSqls=new ArrayList<String>();
			List<Map<String,Object>> paramsUpdate=new ArrayList<Map<String,Object>>();
			List<String> deleteSqls=new ArrayList<String>();
			
			int excelCount=0;
			boolean isSameSheet=true;
			Object isSpecifyOrginal=null;//是否是特殊发票
			String cSBVCode="";//销售发票号，是根据 是否是特殊发票的出来的
			
			for(Map<String,Object> map:todbRsList){
				long start = System.currentTimeMillis();
				
				excelCount++;
				isSameSheet=true;
				//如果第一次和这一次的sheet页不一样，那就新建一个销售发票
				if(!map.get("sheetName").equals(sheetName)){
					Map<String,Object> SBVIDmap=jdbcTemplate.queryForMap("select MAX(ifatherID)+1 as SBVID from UA_Identity where cVouchType='BILLVOUCH' and  cAcc_id='"+user.getZhangt()+"'");
					SBVID=(Integer)SBVIDmap.get("SBVID");
					jdbcTemplate.update("update UA_Identity set ifatherID='"+SBVID+"' where cVouchType='BILLVOUCH' and  cAcc_id='"+user.getZhangt()+"'");
					if(StringUtils.hasText(sheetName)){
						isSameSheet=false;
					}
				}
				
				
//
//				//---------------------------------
//				
//				//原币无税单价 
//				BigDecimal iUnitPrice=(BigDecimal)map.get("rateUnitPrice");
//				//原币含税单价 
//				BigDecimal iTaxUnitPrice=iUnitPrice.multiply(new BigDecimal(1.17));
//				iTaxUnitPrice=iTaxUnitPrice.setScale(20,RoundingMode.HALF_UP);
//				//原币无税金额
//				BigDecimal iMoney=iUnitPrice.multiply(multiplicand)//(BigDecimal)map.get("moneySui");
//				iMoney=iMoney.setScale(20,RoundingMode.HALF_UP)
//				//原币税额
//				BigDecimal iTax=iMoney.multiply(new BigDecimal(1.17));
//				iTax=iTax.setScale(20,RoundingMode.HALF_UP);
//				//原币价税合计
//				BigDecimal iSum=iUnitPrice.multiply(new BigDecimal(1.17));
//				iSum=iSum.setScale(20,RoundingMode.HALF_UP);
//				
//				//本币无税单价
//				BigDecimal iNatUnitPrice=(BigDecimal)map.get("rateUnitPrice");
//				iNatUnitPrice=iNatUnitPrice.setScale(20,RoundingMode.HALF_UP);
//				//本币无税金额 
//				BigDecimal iNatMoney=(BigDecimal)map.get("moneySui");
//				iNatMoney=iNatMoney.setScale(20,RoundingMode.HALF_UP);
//				//本币税额
//				BigDecimal iNatTax=iMoney.multiply(new BigDecimal(1.17));
//				iNatTax=iNatTax.setScale(20,RoundingMode.HALF_UP);
//				//本币价税合计
//				BigDecimal iNatSum=iUnitPrice.multiply(new BigDecimal(1.17));
//				iNatSum=iNatSum.setScale(20,RoundingMode.HALF_UP);
//				//-------------------------------------------------------
				
				//String cInvCode="";
				String cInvCode=(String)map.get("product_id");//存货编码
				
				//存货名称
				String cInvNameSql="select distinct cinvname from inventory where cinvcode ='"+cInvCode+"'";
				String cInvName="";
				try {
					Map<String,Object> cInvNameMap=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(cInvNameSql);
					cInvName=(String)cInvNameMap.get("cinvname");
				} catch(EmptyResultDataAccessException e){

				}
				
				//税率   前面已经定义了
				//int iTaxRate=17;
				
				//换算率 
				String 	iInvExchRateSql="SELECT distinct iChangRate,cComunitCode FROM ComputationUnit" +
						" WHERE (bMainUnit = 0) AND (cGroupCode =(SELECT cgroupcode FROM inventory WHERE cinvcode = '"+cInvCode+"'))";
					
				BigDecimal iInvExchRate=new BigDecimal(0);
				String cUnitID="";

				try {
					Map<String,Object> ciInvExchRateMap=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(iInvExchRateSql);
					iInvExchRate=(BigDecimal)ciInvExchRateMap.get("iChangRate");
					cUnitID=(String)ciInvExchRateMap.get("cComunitCode");
				} catch(EmptyResultDataAccessException e){

				}
				
				
				
				if(!map.get("isSpecify").equals(isSpecifyOrginal)) {
					String cSBVCodeSql="select max(cSBVCode)+1 as cSBVCode from salebillvouch where cvouchtype='"+map.get("isSpecify")+"'";

					try {
						cSBVCodeRs=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(cSBVCodeSql);
					} catch(EmptyResultDataAccessException e){
						cSBVCodeRs=null;
					}
					
					
					if(cSBVCodeRs!=null){
						if(cSBVCodeRs.get("cSBVCode")!=null){
							cSBVCode=cSBVCodeRs.get("cSBVCode")+"";
						}
					}
					if(!StringUtils.hasText(cSBVCode)){
						cSBVCode="0000000001";
					} else {
						if(cSBVCode.length()<10){
							for(int i=cSBVCode.length();i<10;i++){
								cSBVCode="0"+cSBVCode;
							}
						}
					}
				}

				System.out.println("在查dispatch表前所花时间为："+(System.currentTimeMillis()-start));
				//退货标志
				String bReturnFlag=map.get("bReturnFlag")==null?null:map.get("bReturnFlag")+"";
				//单据日期
				Date dDate=(Date)map.get("dcreatedate");//todbRs.getDate("dcreatedate");
				
				//获取DISPATCHLIST表中的数据
				//如果是同个用户，那就不查询了，用以前的，否则的话，就重新查询，这个还没有做，节约性能的
				//还要进行字段优化，只取几个字段,因为字段实在太多，影响效率
				String dispatchSql="select * " +
						" from DispatchList a, DispatchLists b " +
						" where  a.dDate between '"+startDate+"' and '"+endDate+"' " +
								" and b.CINVCODE='"+cInvCode+"'" +//产品,一个excel中，一个EXCEL的产品品番号很多的
								" and a.cCusCode ='"+cCusCode+"'" +//客户编码
								//" and a.SBVID is null";//销售发票还没有关联进去的
								" and isnull(b.iSettleQuantity,0)<>isnull(b.iQuantity,0) " +//已开票数量不等于发货数量的就取出
								" and a.cVerifier is not null " +//cVerifier就是审核人，当发货单没审核的时候他的数量也不做开票的考虑
								" and a.cCloser is null " +
								"  and a.DLID =b.DLID " +
								"order by a.dDate";
				List<Map<String,Object>> dispatchSqlList=dBToolkit.getJdbcTemplate(user.getDbName()).queryForList(dispatchSql);
				//System.out.println(dispatchSql);
				System.out.println("dispatch表中查出来的记录数条数："+dispatchSqlList.size());
					
				if(dispatchSqlList==null || dispatchSqlList.size()==0){
					writer.write( "{success:false,errors:{msg:'发货明细表中没有符合要求的数据。产品编号为："+cInvCode+"'}}");
			     	writer.close();
			     	return;
				}
				
				
//				问题出在这里，当quantity剪掉原来的数据后，小于0的时候，会继续查询下去，所以报错，这个时候，AutoID没有再增加，所以报错，当
//				quantity小于等于0后，应该跳出来重新查询的
				//-----------------------	
				//excel中每行的数量
				BigDecimal quantity=(BigDecimal)map.get("quantity");//注意有没有小数，还在问
				//按规则取出的数量
				BigDecimal iSettleQuantity=null;//这个是往上加的
				int i=0;
				
				//获取明细单的id
				Map<String,Object> AutoIDmap=jdbcTemplate.queryForMap("select MAX(ichildid)+1 as AutoID from UA_Identity where cVouchType='BILLVOUCH' and  cAcc_id='"+user.getZhangt()+"'");
				int AutoID=(Integer)AutoIDmap.get("AutoID");
				System.out.println("AutoID:"+AutoID+"。准备数据的时间是："+(System.currentTimeMillis()-start));
				
				BigDecimal iMoneysss=new BigDecimal(0);//历次循环中的金额
				//List<String> sqls1=new ArrayList<String>();
				for(Map<String,Object> dispatch:dispatchSqlList){
					AutoID++;
					i++;
					if(quantity.compareTo(new BigDecimal(0))==0){
						break;
					}
					//BigDecimal iSettleQuantity=(BigDecimal)dispatch.get("iSettleQuantity");
					//按规则进行判断了，然后返回相应的信息，直接修改数据库，匹配一条 修改一条，并删除一条，这样再读取的时候，就会排除已经修改过的dispatch表中的数据
					
					String cSOCode=(String)dispatch.get("cSOCode");
					String cDLCode=(String)dispatch.get("cDLCode");
					String cbdlcode=(String)dispatch.get("cbdlcode");
					String caddcode=(String)dispatch.get("caddcode");
					
					String cDefine1=(String)dispatch.get("cDefine1");
					String cDefine2=(String)dispatch.get("cDefine2");
					String cDefine3=(String)dispatch.get("cDefine3");
					String cDefine4=dispatch.get("cDefine4")==null?null:DateFormatUtils.ISO_DATE_FORMAT.format(new Date(((Timestamp)dispatch.get("cDefine4")).getTime()));
					Integer cDefine5=(Integer)dispatch.get("cDefine5");
					String cDefine6=dispatch.get("cDefine6")==null?null:DateFormatUtils.ISO_DATE_FORMAT.format(new Date(((Timestamp)dispatch.get("cDefine6")).getTime()));
					Double cDefine7=(Double)dispatch.get("cDefine7");
					String cDefine8=(String)dispatch.get("cDefine8");
					String cDefine9=(String)dispatch.get("cDefine9");
					String cDefine10=(String)dispatch.get("cDefine10");
					String cDefine11=(String)dispatch.get("cDefine11");
					String cDefine12=(String)dispatch.get("cDefine12");
					String cDefine13=(String)dispatch.get("cDefine13");
					String cDefine14=(String)dispatch.get("cDefine14");
					Integer cDefine15=(Integer)dispatch.get("cDefine15");
					Double cDefine16=(Double)dispatch.get("cDefine16");
					
					
					String cDefine22=(String)dispatch.get("cDefine22");
					String cDefine23=(String)dispatch.get("cDefine23");
					String cDefine24=(String)dispatch.get("cDefine24");
					String cDefine25=(String)dispatch.get("cDefine25");
					Double cDefine26=(Double)dispatch.get("cDefine26");
					Double cDefine27=(Double)dispatch.get("cDefine27");
					String cDefine28=(String)dispatch.get("cDefine28");
					String cDefine29=(String)dispatch.get("cDefine29");
					String cDefine30=(String)dispatch.get("cDefine30");
					String cDefine31=(String)dispatch.get("cDefine31");
					String cDefine32=(String)dispatch.get("cDefine32");
					String cDefine33=(String)dispatch.get("cDefine33");
					Integer cDefine34=(Integer)dispatch.get("cDefine34");
					Integer cDefine35=(Integer)dispatch.get("cDefine35");
//					String cDefine36=dispatch.get("cDefine36")==null?null:DateFormatUtils.ISO_DATE_FORMAT.format(new Date(((Timestamp)dispatch.get("cDefine36")).getTime()));
//					String cDefine37=dispatch.get("cDefine37")==null?null:DateFormatUtils.ISO_DATE_FORMAT.format(new Date(((Timestamp)dispatch.get("cDefine37")).getTime()));
					Timestamp cDefine36=(Timestamp)dispatch.get("cDefine36");
					Timestamp cDefine37=(Timestamp)dispatch.get("cDefine37");
					
					String cFree1=(String)dispatch.get("cFree1");
					String cFree2=(String)dispatch.get("cFree2");
					String cFree3=(String)dispatch.get("cFree3");
					String cFree4=(String)dispatch.get("cFree4");
					String cFree5=(String)dispatch.get("cFree5");
					String cFree6=(String)dispatch.get("cFree6");
					String cFree7=(String)dispatch.get("cFree7");
					String cFree8=(String)dispatch.get("cFree8");
					String cFree9=(String)dispatch.get("cFree9");
					String cFree10=(String)dispatch.get("cFree10");
					

					
					if(!map.get("sheetName").equals(sheetName)){
						//先插入到SaleBillVouch和SaleBillVouchs
						String insertSaleBillVouch="insert into " +
							"SaleBillVouch(SBVID,cSBVCode,cVouchType,cSTCode,dDate,cDepCode,cSOCode,cCusCode,cexch_name,iExchRate,iTaxRate" +
							" ,bReturnFlag,cBCode,cMaker,cBusType,bFirst,iDisp,cCusName,cDLCode,iVTid,cSource,ccusbank,ccusaccount,iverifystate,ccusperson,dcreatesystime,caddcode" +
							",cDefine1,cDefine2,cDefine3,cDefine4,cDefine5,cDefine6,cDefine7,cDefine8,cDefine9,cDefine10,cDefine11,cDefine12,cDefine13,cDefine14,cDefine15,cDefine16)" +
							" values('"+SBVID+"','"+cSBVCode+"','"+cVouchType+"',"+(StringUtils.hasLength(cSTCode)?("'"+cSTCode+"'"):null)+",'"+DateFormatUtils.ISO_DATE_FORMAT.format(dDate)+"',"+
							(StringUtils.hasLength(cDepCode)?("'"+cDepCode+"'"):null)+",'"+cSOCode+"','"+
							cCusCode+"','"+cexch_name+"','"+iExchRate+"','"+iTaxRate+"','"+bReturnFlag+"',"+(StringUtils.hasLength(cBCode)?("'"+cBCode+"'"):null)+","+(StringUtils.hasLength(cMaker)?("'"+cMaker+"'"):null)+",'"+cBusType
							+"','"+bFirst+"','"+iDisp+"',"+(StringUtils.hasLength(cCusName)?("'"+cCusName+"'"):null)+",'"+cDLCode+"','"+iVTid+"','"+cSource+"',"+(StringUtils.hasLength(ccusbank)?("'"+ccusbank+"'"):null)+","+(StringUtils.hasLength(ccusaccount)?("'"+ccusaccount+"'"):null)+",'"+iverifystate
							+"',"+(StringUtils.hasLength(ccusperson)?("'"+ccusperson+"'"):null)+",'"+DateFormatUtils.ISO_DATE_FORMAT.format(dcreatesystime)+"',"+(StringUtils.hasLength(caddcode)?("'"+caddcode+"'"):null)+"" +
							"," +(StringUtils.hasLength(cDefine1)?("'"+cDefine1+"'"):null)+
							"," +(StringUtils.hasLength(cDefine2)?("'"+cDefine2+"'"):null)+
							"," +(StringUtils.hasLength(cDefine3)?("'"+cDefine3+"'"):null)+
							"," +(StringUtils.hasLength(cDefine4)?("'"+cDefine4+"'"):null)+
							"," +cDefine5+
							"," +(StringUtils.hasLength(cDefine6)?("'"+cDefine6+"'"):null)+
							"," +cDefine7+
							"," +(StringUtils.hasLength(cDefine8)?("'"+cDefine8+"'"):null)+
							"," +(StringUtils.hasLength(cDefine9)?("'"+cDefine9+"'"):null)+
							"," +(StringUtils.hasLength(cDefine10)?("'"+cDefine10+"'"):null)+
							"," +(StringUtils.hasLength(cDefine11)?("'"+cDefine11+"'"):null)+
							"," +(StringUtils.hasLength(cDefine12)?("'"+cDefine12+"'"):null)+
							"," +(StringUtils.hasLength(cDefine13)?("'"+cDefine13+"'"):null)+
							"," +(StringUtils.hasLength(cDefine14)?("'"+cDefine14+"'"):null)+
							"," +cDefine15+
							"," +cDefine16+
							")";
						sqls.add(insertSaleBillVouch);			
						//System.out.println("-----------插入到SaleBillVouch:"+insertSaleBillVouch);
					}
					
					//----------------------
					//仓库编码 
					//String cWhCodeSql="SELECT distinct cWhCode FROM DispatchLists WHERE (DLID IN " +
					//		" (SELECT t.dlid FROM dispatchlist t INNER JOIN dispatchlists s ON t.dlid = s.dlid  WHERE t.cDLCode = '"+cDLCode+"' AND s.cinvcode = '"+cInvCode+"'))";
					//Map<String,Object> cWhCodeMap=dBToolkit.getJdbcTemplate(user.getDbName()).queryForMap(cWhCodeSql);	
					//String cWhCode=""+ciInvExchRateMap.get("cWhCode");
					String cWhCode=""+dispatch.get("cWhCode");
					//的数量，发票的数量
					BigDecimal iQuantity=new BigDecimal((Double)dispatch.get("iQuantity"));
//					//按规则取出的数量
//					BigDecimal iSettleQuantity=null;//这个是往上加的
					if(dispatch.get("iSettleQuantity")==null){
						iSettleQuantity=new BigDecimal(0);
					} else {
						iSettleQuantity=new BigDecimal((Double)dispatch.get("iSettleQuantity"));;
					}
					iQuantity=iQuantity.subtract(iSettleQuantity);
					
					BigDecimal iSettleNum=null;//这个是往上加的
					if(dispatch.get("iSettleNum")==null){
						iSettleNum=new BigDecimal(0);
					} else {
						iSettleNum=new BigDecimal((Double)dispatch.get("iSettleNum"));;
					}
					
					
					//辅计量数量 
					BigDecimal iNum=iQuantity.multiply(iInvExchRate);
					//批次
					Integer iBatch=(Integer)dispatch.get("iBatch");
					if(iBatch==null){
						iBatch=0;
					}
					//批号
					String cBatch =dispatch.get("cBatch")==null?null:(dispatch.get("cBatch")+"");
					//销售订单子表标识 
					Integer iSOsID =(Integer)dispatch.get("iSOsID");
					if(iSOsID==null){
						iSOsID=0;
					}
					//发货退货单子表标识
					Integer iDLsID =(Integer)dispatch.get("iDLsID");
					if(iDLsID==null){
						iDLsID=0;
					}
					//销售订单号 
					String cSoCode=dispatch.get("cSoCode")+"";
					//订单号
					String cordercode=cSoCode;
					//订单行号 
					Long iorderrowno=(Long)dispatch.get("irowno");
					if(iorderrowno==null){
						iorderrowno=0l;
					}
					
					//如果excel的数量小于 发货明细单种的数量的时候，插入到 发票明细中的数量时 excel的数量，
					//否则插入到发票明细表中的数量就是发货明细表中的数量
					quantity=quantity.setScale(29);
					iQuantity=iQuantity.setScale(29);
					int boolQuantity=quantity.compareTo(iQuantity);
					if(boolQuantity==1) {
						//String cSOCode=dispatch.get
						iSettleQuantity=iSettleQuantity.add(iQuantity);
						quantity=quantity.subtract(iQuantity);
						//System.out.println("1:----------quantity:"+quantity+"---iQuantity:"+iQuantity+"------:iSettleQuantity"+iSettleQuantity);
						if(i==dispatchSqlList.size()){
							System.out.println("错误：有一行的EXCEL数量大于发货单的数量");
							writer.write( "{success:false,errors:{msg:'有一行的EXCEL数量大于发货单的数量。'}}");
					     	writer.close();
					     	return;
						}
						//iSettleNum=iSettleNum+要开的发货数量*（EXCEL不含税金额/EXCEL数量）
						iSettleNum=iSettleNum.add(iQuantity.multiply((BigDecimal)map.get("rateUnitPrice")));
					} else if(boolQuantity==0){
						iSettleQuantity=iSettleQuantity.add(iQuantity);
						quantity=quantity.subtract(iQuantity);
						//System.out.println("2:----------quantity:"+quantity+"---iQuantity:"+iQuantity+"------:iSettleQuantity"+iSettleQuantity);
					
						//iSettleNum=iSettleNum+要开的发货数量*（EXCEL不含税金额/EXCEL数量）
						iSettleNum=iSettleNum.add(iQuantity.multiply((BigDecimal)map.get("rateUnitPrice")));
					} else {  
						iSettleQuantity=iSettleQuantity.add(quantity);
						//quantity=quantity.subtract(iQuantity);
						iQuantity=quantity;//这里赋值没有关系，因为当这步执行完后，就跳出去重新查询了
						
						//iSettleNum=iSettleNum+要开的发货数量*（EXCEL不含税金额/EXCEL数量）
						iSettleNum=iSettleNum.add(iQuantity.multiply((BigDecimal)map.get("rateUnitPrice")));
						
						//System.out.println("3:----------quantity:"+quantity+"---iQuantity:"+iQuantity+"------:iSettleQuantity"+iSettleQuantity);
					}
					
//					//获取明细单的id,移到外面去
//					Map<String,Object> AutoIDmap=jdbcTemplate.queryForMap("select MAX(ichildid)+1 as AutoID from UA_Identity where cVouchType='BILLVOUCH' and  cAcc_id='"+user.getZhangt()+"'");
//					int AutoID=(Integer)AutoIDmap.get("AutoID");
					//暂时注视掉
					//jdbcTemplate.update("update UA_Identity set ifatherID='"+SBVID+"',ichildid='"+AutoID+"'");
					//移到后面去，因为只需获取到最大的值就可以了
					//jdbcTemplate.update("update UA_Identity set ichildid='"+AutoID+"' where cVouchType='BILLVOUCH' and  cAcc_id='"+user.getZhangt()+"'");
					//System.out.println("AutoID:"+AutoID);
					
					

					//---------------------------------
					
					//原币无税单价 quantity  moneySui
					BigDecimal iUnitPrice=((BigDecimal)map.get("moneySui")).divide((BigDecimal)map.get("quantity"),2, RoundingMode.HALF_UP); //(BigDecimal)map.get("rateUnitPrice");
					//iUnitPrice.
					//原币含税单价 
					BigDecimal iTaxUnitPrice=iUnitPrice.multiply(new BigDecimal(1.17));
					iTaxUnitPrice=iTaxUnitPrice.setScale(2,RoundingMode.HALF_UP);
					//原币无税金额
					BigDecimal iMoney=iUnitPrice.multiply(iQuantity);//(BigDecimal)map.get("moneySui");
					iMoney=iMoney.setScale(2,RoundingMode.HALF_UP);
					if(boolQuantity<=0){//如果是最后一笔了的话,excel中所有的钱 减去前面的钱
						iMoney=((BigDecimal)map.get("moneySui")).subtract(iMoneysss);
					} else {
						iMoneysss=iMoneysss.add(iMoney);
					}
					//原币税额
					BigDecimal iTax=iMoney.multiply(new BigDecimal(0.17));
					iTax=iTax.setScale(2,RoundingMode.HALF_UP);
					//原币价税合计
					BigDecimal iSum=iMoney.multiply(new BigDecimal(1.17));
					iSum=iSum.setScale(2,RoundingMode.HALF_UP);
					
					
//					//本币无税单价
//					BigDecimal iNatUnitPrice=((BigDecimal)map.get("moneySui")).divide((BigDecimal)map.get("quantity"),29, RoundingMode.HALF_UP);//(BigDecimal)map.get("rateUnitPrice");
//					iNatUnitPrice=iNatUnitPrice.setScale(29,RoundingMode.HALF_UP);
//					//本币无税金额 
//					BigDecimal iNatMoney=iNatUnitPrice.multiply(iQuantity);//(BigDecimal)map.get("moneySui");
//					iNatMoney=iNatMoney.setScale(29,RoundingMode.HALF_UP);
//					//本币税额
//					BigDecimal iNatTax=iNatMoney.multiply(new BigDecimal(0.17));
//					iNatTax=iNatTax.setScale(29,RoundingMode.HALF_UP);
//					//本币价税合计
//					BigDecimal iNatSum=iNatMoney.multiply(new BigDecimal(1.17));
//					iNatSum=iNatSum.setScale(29,RoundingMode.HALF_UP);
					
					BigDecimal iNatUnitPrice=iUnitPrice;
					BigDecimal iNatMoney=iMoney;
					BigDecimal iNatTax=iTax;
					BigDecimal iNatSum=iSum;
					
					//
					//-------------------------------------------------------
					
					
					//-----------------------
//					String insertLine="insert into " +
//						" SaleBillVouchs(SBVID,AutoID,cWhCode,cInvCode,iQuantity,iNum,iUnitPrice,iTaxUnitPrice,iMoney,iTax,iSum" +
//						" ,iNatUnitPrice,iNatMoney,iNatTax,iNatSum,iBatch,cBatch" +
//						" ,iSOsID,iDLsID,KL,KL2,cInvName,iTaxRate,iInvExchRate,cUnitID,cSoCode,bcosting,cordercode,iorderrowno,cbdlcode" +
//						",cDefine22,cDefine23,cDefine24,cDefine25,cDefine26,cDefine27,cDefine28,cDefine29,cDefine30,cDefine31,cDefine32,cDefine33,cDefine34,cDefine35,cDefine36,cDefine37" +
//						",cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10)" +
//						" values('"+SBVID+"','"+AutoID+"','"+cWhCode+"','"+cInvCode+"','"+iQuantity+"','"+iNum+"','"+
//						iUnitPrice+"','"+iTaxUnitPrice+"',"+iMoney+","+iTax+","+iSum+",'"+iNatUnitPrice
//						+"',"+iNatMoney+","+iNatTax+","+iNatSum+",'"+iBatch+"',"+(cBatch==null?null:("'"+cBatch+"'")) +",'"+iSOsID+"','"+iDLsID+"','"+KL+"','"+KL2
//						+"',"+(StringUtils.hasLength(cInvName)?("'"+cInvName+"'"):null)+",'"+iTaxRate+"','"+iInvExchRate+"',"+(StringUtils.hasLength(cUnitID)?("'"+cUnitID+"'"):null)+",'"
//						 +cSoCode+"','"+bcosting+"','"+cordercode+"','"+iorderrowno+"',"+(StringUtils.hasLength(cbdlcode)?("'"+cbdlcode+"'"):null)+"" +
//						"," +(StringUtils.hasLength(cDefine22)?("'"+cDefine22+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine23)?("'"+cDefine23+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine24)?("'"+cDefine24+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine25)?("'"+cDefine25+"'"):null)+
//						"," +cDefine26+
//						"," +cDefine27+
//						"," +(StringUtils.hasLength(cDefine28)?("'"+cDefine28+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine29)?("'"+cDefine29+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine30)?("'"+cDefine30+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine31)?("'"+cDefine31+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine32)?("'"+cDefine32+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine33)?("'"+cDefine33+"'"):null)+
//						"," +cDefine34+
//						"," +cDefine35+
//						"," +(StringUtils.hasLength(cDefine36)?("'"+cDefine36+"'"):null)+
//						"," +(StringUtils.hasLength(cDefine37)?("'"+cDefine37+"'"):null)+
//						"," +(StringUtils.hasLength(cFree1)?("'"+cFree1+"'"):null)+
//						"," +(StringUtils.hasLength(cFree2)?("'"+cFree2+"'"):null)+
//						"," +(StringUtils.hasLength(cFree3)?("'"+cFree3+"'"):null)+
//						"," +(StringUtils.hasLength(cFree4)?("'"+cFree4+"'"):null)+
//						"," +(StringUtils.hasLength(cFree5)?("'"+cFree5+"'"):null)+
//						"," +(StringUtils.hasLength(cFree6)?("'"+cFree6+"'"):null)+
//						"," +(StringUtils.hasLength(cFree7)?("'"+cFree7+"'"):null)+
//						"," +(StringUtils.hasLength(cFree8)?("'"+cFree8+"'"):null)+
//						"," +(StringUtils.hasLength(cFree9)?("'"+cFree9+"'"):null)+
//						"," +(StringUtils.hasLength(cFree10)?("'"+cFree10+"'"):null)+
//						")";
//					sqls.add(insertLine);
					
					Map<String,Object> param=new HashMap<String,Object>();
					param.put("SBVID", SBVID);
					param.put("AutoID", AutoID);
					param.put("cWhCode", cWhCode);
					param.put("cInvCode", cInvCode);
					param.put("iQuantity", iQuantity);
					param.put("iNum", iNum);
					param.put("iUnitPrice", iUnitPrice);
					param.put("iTaxUnitPrice", iTaxUnitPrice);
					param.put("iMoney", iMoney);
					param.put("iTax", iTax);
					param.put("iSum", iSum);
					param.put("iNatUnitPrice", iNatUnitPrice);
					param.put("iNatMoney", iNatMoney);
					param.put("iNatTax", iNatTax);
					param.put("iNatSum", iNatSum);
					param.put("iBatch", iBatch);
					param.put("cBatch", cBatch==null?null:("'"+cBatch+"'"));
					param.put("iSOsID", iSOsID);
					param.put("iDLsID", iDLsID);
					param.put("KL", KL);
					param.put("KL2", KL2);
					param.put("cInvName", (StringUtils.hasLength(cInvName)?("'"+cInvName+"'"):null));
					param.put("iTaxRate", iTaxRate);
					param.put("iInvExchRate", iInvExchRate);
					param.put("cUnitID",(StringUtils.hasLength(cUnitID)?("'"+cUnitID+"'"):null) );
					param.put("cSoCode", cSoCode);
					param.put("bcosting", bcosting);
					param.put("cordercode", cordercode);
					param.put("iorderrowno", iorderrowno);
					param.put("cbdlcode", (StringUtils.hasLength(cbdlcode)?("'"+cbdlcode+"'"):null));
					
					param.put("cDefine22", (StringUtils.hasLength(cDefine22)?("'"+cDefine22+"'"):null));
					param.put("cDefine23", (StringUtils.hasLength(cDefine23)?("'"+cDefine23+"'"):null));
					param.put("cDefine24", (StringUtils.hasLength(cDefine24)?("'"+cDefine24+"'"):null));
					param.put("cDefine25", (StringUtils.hasLength(cDefine25)?("'"+cDefine25+"'"):null));
					param.put("cDefine26", cDefine26);
					param.put("cDefine27", cDefine27);
					param.put("cDefine28", (StringUtils.hasLength(cDefine28)?("'"+cDefine28+"'"):null));
					param.put("cDefine29", (StringUtils.hasLength(cDefine29)?("'"+cDefine29+"'"):null));
					param.put("cDefine30", (StringUtils.hasLength(cDefine30)?("'"+cDefine30+"'"):null));
					param.put("cDefine31", (StringUtils.hasLength(cDefine31)?("'"+cDefine31+"'"):null));
					param.put("cDefine32", (StringUtils.hasLength(cDefine32)?("'"+cDefine32+"'"):null));
					param.put("cDefine33", (StringUtils.hasLength(cDefine33)?("'"+cDefine33+"'"):null));
					param.put("cDefine34", cDefine34);
					param.put("cDefine35", cDefine35);
					param.put("cDefine36", cDefine36!=null?cDefine36:null);
					param.put("cDefine37", cDefine37!=null?cDefine36:null);
					param.put("cFree1", (StringUtils.hasLength(cFree1)?("'"+cFree1+"'"):null));
					param.put("cFree2", (StringUtils.hasLength(cFree2)?("'"+cFree2+"'"):null));
					param.put("cFree3", (StringUtils.hasLength(cFree3)?("'"+cFree3+"'"):null));
					param.put("cFree4", (StringUtils.hasLength(cFree4)?("'"+cFree4+"'"):null));
					param.put("cFree5", (StringUtils.hasLength(cFree5)?("'"+cFree5+"'"):null));
					param.put("cFree6", (StringUtils.hasLength(cFree6)?("'"+cFree6+"'"):null));
					param.put("cFree7", (StringUtils.hasLength(cFree7)?("'"+cFree7+"'"):null));
					param.put("cFree8", (StringUtils.hasLength(cFree8)?("'"+cFree8+"'"):null));
					param.put("cFree9", (StringUtils.hasLength(cFree9)?("'"+cFree9+"'"):null));
					param.put("cFree10",(StringUtils.hasLength(cFree10)?("'"+cFree10+"'"):null));

					paramsLine.add(param);
					
					
					//System.out.println("-----------插入到SaleBillVouchs------:"+insertLine);
					
//					//再反写dispatchlist和dispatchlists
//					if(!map.get("sheetName").equals(sheetName)){
//						//String updateDispatchSql="update dispatchlist set cSBVCode='"+cSBVCode+"' ,SBVID='"+SBVID+"' where DLID='"+dispatch.get("DLID")+"'";
//						//sqls.add(updateDispatchSql);
//						//System.out.println("-----------更新dispatchlist的数据------:"+updateDispatchSql);
//					}
					
					//String updateDispatchsSql="update dispatchlists set iSettleQuantity='"+iSettleQuantity+"',iSettleNum='"+iSettleNum+"'  where AutoID ='"+ dispatch.get("AutoID")+"'";
					//sqls.add(updateDispatchsSql);
					//updateSqls.add(updateDispatchsSql);//20100924
					Map<String,Object> updateParams=new HashMap<String,Object>();
					updateParams.put("iSettleQuantity", iSettleQuantity);
					updateParams.put("iSettleNum", iSettleNum);
					updateParams.put("AutoID", dispatch.get("AutoID"));
					paramsUpdate.add(updateParams);
					
					//System.out.println("-----------更新dispatchlists的数据------:"+updateDispatchsSql);
					sheetName=""+map.get("sheetName");

					
					//如果相等或小于等于0，表示这个excel的一行处理完了，要处理下一行了，要重新查询发货表了，因为下一行可能产品编码不一样了
					//删除相应的数据
					if(boolQuantity<=0){
						String deleteSql="delete UA_excelToDb_"+user.getId()+" where id="+map.get("id");
						//sqls.add(deleteSql);
						deleteSqls.add(deleteSql);//20100924
						
						//System.out.println("-----------删除领事表的数据------:"+deleteSql);
						break;
					} else {
//						String updateSql="update UA_excelToDb_"+user.getId()+" set quantity='"+quantity+"' where id="+map.get("id");
//						sqls1.add(updateSql);
//						System.out.println("-----------更新临时表的数据------:"+updateSql);
					}	
					
					if(boolQuantity<=0) {
						break;
					}
					
				}//for(Map<String,Object> dispatch:dispatchSqlList)
				jdbcTemplate.update("update UA_Identity set ichildid='"+AutoID+"' where cVouchType='BILLVOUCH' and  cAcc_id='"+user.getZhangt()+"'");

				
				long end = System.currentTimeMillis();
				System.out.println("查询分析,所用时间为: " + (end - start) + " ms");
				if((!isSameSheet) || excelCount==todbRsList.size()){
					//一个sheet页，一张发票，所以一个sheet页执行一次
					//boolean bool=batchUpdate(sqls,user);
					//sqls.clear();
					boolean bool=batchUpdate2(sqls,paramsLine,paramsUpdate,deleteSqls,user);//20100924
					sqls.clear();
					paramsUpdate.clear();
					paramsUpdate.clear();//20100924
					deleteSqls.clear();//20100924
					if(!bool){
						writer.write( "{success:false,errors:{msg:'后台发生错误，请联系管理员，查看日志！'}}");
				     	writer.close();
				     	return;
					}
				}

				
				
			}//excel的循环for(Map<String,Object> map:todbRsList){
			

			
			//何种情况下，才能设置为正确呢
			//如果这个模块的数据都没有了那就结束
			String stateSql="select count(*) from UA_excelToDb_"+user.getId()+" where model='"+model+"'";
			int stateSize=dBToolkit.getJdbcTemplate(user.getDbName()).queryForInt(stateSql);
			if(stateSize<=0){
				logonController.setState(user, model, 4);
			}

			
			//dBToolkit.closeConnection(conn);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			//System.out.println(sql);
			e.printStackTrace();
			writer.write( "{success:false,errors:{msg:'后台发生错误。'}}");
	     	writer.close();
	     	return;
		}
		
	
		writer.write( "{success:true}");
     	writer.close();
     	return;
		
	}
	
	public boolean batchUpdate2(final List<String> sqls,final List<Map<String,Object>> paramsLine,final List<Map<String,Object>> updateSqlParams,final List<String> deleteSqls, final User user){
		
		Connection conn =null;
		try {
			conn = dBToolkit.getJdbcTemplate(user.getDbName()).getDataSource().getConnection();
			conn.setAutoCommit(false);
			//PreparedStatement stat = conn.prepareStatement("insert into test1 values(?)");
			Statement stat=conn.createStatement();

	        Long start=System.currentTimeMillis();
	       
	        for(int i=0;i<sqls.size();i++){
				System.out.println(i+":"+sqls.get(i));
				stat.execute(sqls.get(i));
			}

	        final String updateDispatchsSql="update dispatchlists set iSettleQuantity=?,iSettleNum=?  where AutoID =? ";
	        PreparedStatement ps = conn.prepareStatement(updateDispatchsSql);
            
            for(int i=0;i< updateSqlParams.size();i++){
            	System.out.println(i+":更新数据");
            	ps.setObject(1, updateSqlParams.get(i).get("iSettleQuantity"));   
                ps.setObject(2, updateSqlParams.get(i).get("iSettleNum"));   
                ps.setObject(3, updateSqlParams.get(i).get("AutoID"));
                ps.addBatch();
            }
            
            ps.executeBatch();
            ps.clearBatch();
            
            final String sql="insert into " +
				" SaleBillVouchs(SBVID,AutoID,cWhCode,cInvCode,iQuantity,iNum,iUnitPrice,iTaxUnitPrice,iMoney,iTax,iSum" +
				" ,iNatUnitPrice,iNatMoney,iNatTax,iNatSum,iBatch,cBatch" +
				" ,iSOsID,iDLsID,KL,KL2,cInvName,iTaxRate,iInvExchRate,cUnitID,cSoCode,bcosting,cordercode,iorderrowno,cbdlcode" +
				",cDefine22,cDefine23,cDefine24,cDefine25,cDefine26,cDefine27,cDefine28,cDefine29,cDefine30,cDefine31,cDefine32,cDefine33,cDefine34,cDefine35,cDefine36,cDefine37" +
				",cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10)" +//56个字段
				" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement ps1 = conn.prepareStatement(sql);
            
            for(int i=0;i<paramsLine.size();i++){
            	System.out.println(i+":插入明细数据");
            	ps1.setObject(1, paramsLine.get(i).get("SBVID"));   
                ps1.setObject(2, paramsLine.get(i).get("AutoID"));
                ps1.setObject(3, paramsLine.get(i).get("cWhCode"));   
                ps1.setObject(4, paramsLine.get(i).get("cInvCode"));  
                ps1.setObject(5, paramsLine.get(i).get("iQuantity"));
                ps1.setObject(6, paramsLine.get(i).get("iNum"));
                ps1.setObject(7, paramsLine.get(i).get("iUnitPrice"));
                ps1.setObject(8, paramsLine.get(i).get("iTaxUnitPrice"));
                ps1.setObject(9, paramsLine.get(i).get("iMoney"));
                ps1.setObject(10, paramsLine.get(i).get("iTax"));
                ps1.setObject(11, paramsLine.get(i).get("iSum"));
                ps1.setObject(12, paramsLine.get(i).get("iNatUnitPrice"));
                ps1.setObject(13, paramsLine.get(i).get("iNatMoney"));
                ps1.setObject(14, paramsLine.get(i).get("iNatTax"));
                ps1.setObject(15, paramsLine.get(i).get("iNatSum"));
                ps1.setObject(16, paramsLine.get(i).get("iBatch"));
                ps1.setObject(17, paramsLine.get(i).get("cBatch"));
                ps1.setObject(18, paramsLine.get(i).get("iSOsID"));
                ps1.setObject(19, paramsLine.get(i).get("iDLsID"));
                ps1.setObject(20, paramsLine.get(i).get("KL"));
                ps1.setObject(21, paramsLine.get(i).get("KL2"));
                ps1.setObject(22, paramsLine.get(i).get("cInvName"));
                ps1.setObject(23, paramsLine.get(i).get("iTaxRate"));
                ps1.setObject(24, paramsLine.get(i).get("iInvExchRate"));
                ps1.setObject(25, paramsLine.get(i).get("cUnitID"));
                ps1.setObject(26, paramsLine.get(i).get("cSoCode"));
                ps1.setObject(27, paramsLine.get(i).get("bcosting"));
                ps1.setObject(28, paramsLine.get(i).get("cordercode"));
                ps1.setObject(29, paramsLine.get(i).get("iorderrowno"));
                ps1.setObject(30, paramsLine.get(i).get("cbdlcode"));
                ps1.setObject(31, paramsLine.get(i).get("cDefine22"));
                ps1.setObject(32, paramsLine.get(i).get("cDefine23"));
                ps1.setObject(33, paramsLine.get(i).get("cDefine24"));
                ps1.setObject(34, paramsLine.get(i).get("cDefine25"));
                ps1.setObject(35, paramsLine.get(i).get("cDefine26"));
                ps1.setObject(36, paramsLine.get(i).get("cDefine27"));
                ps1.setObject(37, paramsLine.get(i).get("cDefine28"));
                ps1.setObject(38, paramsLine.get(i).get("cDefine29"));
                ps1.setObject(39, paramsLine.get(i).get("cDefine30"));
                ps1.setObject(40, paramsLine.get(i).get("cDefine31"));
                ps1.setObject(41, paramsLine.get(i).get("cDefine32"));
                ps1.setObject(42, paramsLine.get(i).get("cDefine33"));
                ps1.setObject(43, paramsLine.get(i).get("cDefine34"));
                ps1.setObject(44, paramsLine.get(i).get("cDefine35"));
                ps1.setObject(45, paramsLine.get(i).get("cDefine36"));
                ps1.setObject(46, paramsLine.get(i).get("cDefine37"));
                ps1.setObject(47, paramsLine.get(i).get("cFree1"));
                ps1.setObject(48, paramsLine.get(i).get("cFree2"));
                ps1.setObject(49, paramsLine.get(i).get("cFree3"));
                ps1.setObject(50, paramsLine.get(i).get("cFree4"));
                ps1.setObject(51, paramsLine.get(i).get("cFree5"));
                ps1.setObject(52, paramsLine.get(i).get("cFree6"));
                ps1.setObject(53, paramsLine.get(i).get("cFree7"));
                ps1.setObject(54, paramsLine.get(i).get("cFree8"));
                ps1.setObject(55, paramsLine.get(i).get("cFree9"));
                ps1.setObject(56, paramsLine.get(i).get("cFree10"));
                ps1.addBatch();
            }
            
            ps1.executeBatch();
            ps1.clearBatch();

            for(int i=0;i<deleteSqls.size();i++){
				//System.out.println(i+":删除数据："+deleteSqls.get(i));
				//dBToolkit.getJdbcTemplate(user.getDbName()).execute(deleteSqls.get(i));
				stat.execute(deleteSqls.get(i));
			}
			

			
			conn.commit();
			
			//System.out.println("sql的长度是"+sqls.size());
			long end = System.currentTimeMillis();
			System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms。开始："+start+".结束："+end);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return true;

	}
	 /**
	  * 事务处理,批量更新List<String> String是SQL语句
	  * 
	  * @return
	  */
	 public boolean batchUpdate1(final List<String> sqls,final List<Map<String,Object>> paramsLine,final List<Map<String,Object>> updateSqlParams,final List<String> deleteSqls, final User user) {
		 
		 //List<Map<String,Object>> paramsLine=new ArrayList<Map<String,Object>>();
		 
		 if(paramsLine==null || paramsLine.size()==0){
			 return true;
		 }
		 
		 System.out.println("sql的长度是"+sqls.size());
		 //final int count=paramsLine.size();
		 
		 final String sql="insert into " +
			" SaleBillVouchs(SBVID,AutoID,cWhCode,cInvCode,iQuantity,iNum,iUnitPrice,iTaxUnitPrice,iMoney,iTax,iSum" +
			" ,iNatUnitPrice,iNatMoney,iNatTax,iNatSum,iBatch,cBatch" +
			" ,iSOsID,iDLsID,KL,KL2,cInvName,iTaxRate,iInvExchRate,cUnitID,cSoCode,bcosting,cordercode,iorderrowno,cbdlcode" +
			",cDefine22,cDefine23,cDefine24,cDefine25,cDefine26,cDefine27,cDefine28,cDefine29,cDefine30,cDefine31,cDefine32,cDefine33,cDefine34,cDefine35,cDefine36,cDefine37" +
			",cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10)" +//56个字段
			" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		 
		 final String updateDispatchsSql="update dispatchlists set iSettleQuantity=?,iSettleNum=?  where AutoID =? ";
	  try {
		  TransactionTemplate tt =new TransactionTemplate(dBToolkit.getTransactionManager(user.getDbName()));
			tt.execute(new TransactionCallback() {
				public Object doInTransaction(TransactionStatus status) {
					long start = System.currentTimeMillis();
					//dBToolkit.getJdbcTemplate(user.getDbName()).batchUpdate(sqls.toArray(new String[sqls.size()]));
					for(int i=0;i<sqls.size();i++){
						System.out.println(i+":"+sqls.get(i));
						dBToolkit.getJdbcTemplate(user.getDbName()).execute(sqls.get(i));
					}
					
					
					int[] updateCounts =dBToolkit.getJdbcTemplate(user.getDbName()).batchUpdate(   
							updateDispatchsSql,    
			                new BatchPreparedStatementSetter(){   
			  
			                    public int getBatchSize() {   
			                        return updateSqlParams.size();   
			                    }   
			  
			                    public void setValues(PreparedStatement ps, int i) throws SQLException {   
			                    	System.out.println(i+":更新数据");
			                        ps.setObject(1, updateSqlParams.get(i).get("iSettleQuantity"));   
			                        ps.setObject(2, updateSqlParams.get(i).get("iSettleNum"));   
			                        ps.setObject(3, updateSqlParams.get(i).get("AutoID"));   
			                    }   
			                       
			                });   

					long end = System.currentTimeMillis();
					System.out.println("更新数据------结束,所用时间为: " + (end - start)/60000 + " ms");
					//System.out.println("更新数据------结束");
					
					int[] insertCounts =dBToolkit.getJdbcTemplate(user.getDbName()).batchUpdate(   
							sql,    
			                new BatchPreparedStatementSetter(){   
			  
			                    public int getBatchSize() {   
			                        return paramsLine.size();   
			                    }   
			  
			                    public void setValues(PreparedStatement ps, int i) throws SQLException {  
			                    	System.out.println(i+":插入明细数据");
			                        ps.setObject(1, paramsLine.get(i).get("SBVID"));   
			                        ps.setObject(2, paramsLine.get(i).get("AutoID"));
			                        ps.setObject(3, paramsLine.get(i).get("cWhCode"));   
			                        ps.setObject(4, paramsLine.get(i).get("cInvCode"));  
			                        ps.setObject(5, paramsLine.get(i).get("iQuantity"));
			                        ps.setObject(6, paramsLine.get(i).get("iNum"));
			                        ps.setObject(7, paramsLine.get(i).get("iUnitPrice"));
			                        ps.setObject(8, paramsLine.get(i).get("iTaxUnitPrice"));
			                        ps.setObject(9, paramsLine.get(i).get("iMoney"));
			                        ps.setObject(10, paramsLine.get(i).get("iTax"));
			                        ps.setObject(11, paramsLine.get(i).get("iSum"));
			                        ps.setObject(12, paramsLine.get(i).get("iNatUnitPrice"));
			                        ps.setObject(13, paramsLine.get(i).get("iNatMoney"));
			                        ps.setObject(14, paramsLine.get(i).get("iNatTax"));
			                        ps.setObject(15, paramsLine.get(i).get("iNatSum"));
			                        ps.setObject(16, paramsLine.get(i).get("iBatch"));
			                        ps.setObject(17, paramsLine.get(i).get("cBatch"));
			                        ps.setObject(18, paramsLine.get(i).get("iSOsID"));
			                        ps.setObject(19, paramsLine.get(i).get("iDLsID"));
			                        ps.setObject(20, paramsLine.get(i).get("KL"));
			                        ps.setObject(21, paramsLine.get(i).get("KL2"));
			                        ps.setObject(22, paramsLine.get(i).get("cInvName"));
			                        ps.setObject(23, paramsLine.get(i).get("iTaxRate"));
			                        ps.setObject(24, paramsLine.get(i).get("iInvExchRate"));
			                        ps.setObject(25, paramsLine.get(i).get("cUnitID"));
			                        ps.setObject(26, paramsLine.get(i).get("cSoCode"));
			                        ps.setObject(27, paramsLine.get(i).get("bcosting"));
			                        ps.setObject(28, paramsLine.get(i).get("cordercode"));
			                        ps.setObject(29, paramsLine.get(i).get("iorderrowno"));
			                        ps.setObject(30, paramsLine.get(i).get("cbdlcode"));
			                        ps.setObject(31, paramsLine.get(i).get("cDefine22"));
			                        ps.setObject(32, paramsLine.get(i).get("cDefine23"));
			                        ps.setObject(33, paramsLine.get(i).get("cDefine24"));
			                        ps.setObject(34, paramsLine.get(i).get("cDefine25"));
			                        ps.setObject(35, paramsLine.get(i).get("cDefine26"));
			                        ps.setObject(36, paramsLine.get(i).get("cDefine27"));
			                        ps.setObject(37, paramsLine.get(i).get("cDefine28"));
			                        ps.setObject(38, paramsLine.get(i).get("cDefine29"));
			                        ps.setObject(39, paramsLine.get(i).get("cDefine30"));
			                        ps.setObject(40, paramsLine.get(i).get("cDefine31"));
			                        ps.setObject(41, paramsLine.get(i).get("cDefine32"));
			                        ps.setObject(42, paramsLine.get(i).get("cDefine33"));
			                        ps.setObject(43, paramsLine.get(i).get("cDefine34"));
			                        ps.setObject(44, paramsLine.get(i).get("cDefine35"));
			                        ps.setObject(45, paramsLine.get(i).get("cDefine36"));
			                        ps.setObject(46, paramsLine.get(i).get("cDefine37"));
			                        ps.setObject(47, paramsLine.get(i).get("cFree1"));
			                        ps.setObject(48, paramsLine.get(i).get("cFree2"));
			                        ps.setObject(49, paramsLine.get(i).get("cFree3"));
			                        ps.setObject(50, paramsLine.get(i).get("cFree4"));
			                        ps.setObject(51, paramsLine.get(i).get("cFree5"));
			                        ps.setObject(52, paramsLine.get(i).get("cFree6"));
			                        ps.setObject(53, paramsLine.get(i).get("cFree7"));
			                        ps.setObject(54, paramsLine.get(i).get("cFree8"));
			                        ps.setObject(55, paramsLine.get(i).get("cFree9"));
			                        ps.setObject(56, paramsLine.get(i).get("cFree10"));
 
			                    }   
			                       
			               });   

					System.out.println("插入明细数据------结束");
					
					
					
					for(int i=0;i<deleteSqls.size();i++){
						System.out.println(i+":删除数据："+deleteSqls.get(i));
						dBToolkit.getJdbcTemplate(user.getDbName()).execute(deleteSqls.get(i));
					}
					//System.out.println("sql的长度是"+sqls.size());
					end = System.currentTimeMillis();
					System.out.println("数据导入完毕,所用时间为: " + (end - start)/60000 + " ms");
					return null;
				}
			});
		  
		  
	  } catch (Exception e) {
		 
	   e.printStackTrace();
	   return false;
	  }
	  return true;
	 }

	 
	 /**
	  * 事务处理,批量更新List<String> String是SQL语句
	  * 
	  * @return
	  */
	 public boolean batchUpdate(final List<String> sqls, final User user) {
		 
		 if(sqls==null || sqls.size()==0){
			 return true;
		 }
		 
		 System.out.println("sql的长度是"+sqls.size());
		 final int count=sqls.size();
	  try {
		  TransactionTemplate tt =new TransactionTemplate(dBToolkit.getTransactionManager(user.getDbName()));
			tt.execute(new TransactionCallback() {
				public Object doInTransaction(TransactionStatus status) {
					//dBToolkit.getJdbcTemplate(user.getDbName()).batchUpdate(sqls.toArray(new String[sqls.size()]));
					for(int i=0;i<sqls.size();i++){
						System.out.println(i+":"+sqls.get(i));
						dBToolkit.getJdbcTemplate(user.getDbName()).execute(sqls.get(i));
					}

					//System.out.println("sql的长度是"+sqls.size());
					return null;
				}
			});
		  
//		  long start = System.currentTimeMillis();
//		  for(int i=0;i<sqls.size();i++){
//			  Connection mysqlConn=dBToolkit.getJdbcTemplate(user.getDbName()).getDataSource().getConnection();
//			  PreparedStatement mysqlPs =mysqlConn.prepareStatement(sqls.get(i));
//			  mysqlConn.setAutoCommit(false);
//		  }
//		  dBToolkit.getJdbcTemplate(user.getDbName()).batchUpdate(sql, pss)
//		  long end = System.currentTimeMillis();
//          System.out.println("数据导入完毕,所用时间为: " + (end - start) + " ms");


		  
	  } catch (Exception e) {
		 
	   e.printStackTrace();
	   return false;
	  }
	  return true;
	 }



	 
}
